---
title: "SQL-PLOC-02: Provider locations by provider"
summary: Retrieve all locations for a given provider
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-08-21
library: SAIF.Business.View.Provider
reference_type: embedded
---

# SQL-PLOC-02 Provider locations by provider

Retrieves every `PROVIDER_LOCATION` record belonging to a single provider, used to populate a
`clsProviderLocationCollection` filtered by `ProviderID`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-PLOC-02 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (BOF criteria constant) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsProviderLocationCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:ProviderID` | Input | Decimal | N/A | `PROVIDER_LOCATION.PROVIDER_SEQ_NUM` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PROVIDER_LOCATION](../database-tables.md#provider_location) | SELECT | `APPROVED_FLAG`, `APPROVED_USER`, `BUSINESS_FLAG`, `CRITICAL_ACCESS_HOSPITAL_FLAG`, `BUSINESS_NAME`, `FAX_NUM`, `FIRST_NAME`, `INELIGIBILITY_FLAG`, `LAST_NAME`, `MIDDLE_NAME`, `PAYEE_NAME`, `PHONE_AREA_CODE`, `PHONE_NUM`, `PHONE_NUM_EXT`, `POSSIBLE_FRAUD_FLAG`, `PRIMARY_LOCATION_FLAG`, `PROVIDER_LOCATION_SEQ_NUM`, `PROVIDER_SEQ_NUM`, `PROVIDER_TYPE_CODE`, `RECENT_ACTIVITY_FLAG`, `TOLL_FREE_FAX_NUM`, `TOLL_FREE_PHONE_NUM`, `UPDATE_DATE`, `UPDATE_NUM`, `UPDATE_USER`, `WEB_SITE_ADDRESS`, `EMAIL_ADDR` |

## Sample SQL

```sql
-- Provider locations by provider
SELECT
  pl.APPROVED_FLAG                 AS IsApproved,
  pl.APPROVED_USER                 AS ApprovedUser,
  pl.BUSINESS_FLAG                 AS IsBusiness,
  pl.CRITICAL_ACCESS_HOSPITAL_FLAG AS IsCriticalAccessHospital,
  pl.BUSINESS_NAME                 AS BusinessName,
  pl.FAX_NUM                       AS FaxNum,
  pl.FIRST_NAME                    AS FirstName,
  pl.INELIGIBILITY_FLAG            AS IsIneligible,
  pl.LAST_NAME                     AS LastName,
  pl.MIDDLE_NAME                   AS MiddleName,
  pl.PAYEE_NAME                    AS PayeeName,
  pl.PHONE_AREA_CODE               AS PhoneAreaCode,
  pl.PHONE_NUM                     AS PhoneNum,
  pl.PHONE_NUM_EXT                 AS PhoneNumExt,
  pl.POSSIBLE_FRAUD_FLAG           AS IsPossibleFraud,
  pl.PRIMARY_LOCATION_FLAG         AS IsPrimaryLocation,
  pl.PROVIDER_LOCATION_SEQ_NUM     AS ProviderLocationID,
  pl.PROVIDER_SEQ_NUM              AS ProviderID,
  pl.PROVIDER_TYPE_CODE            AS ProviderTypeCode,
  pl.RECENT_ACTIVITY_FLAG          AS HasRecentActivity,
  pl.TOLL_FREE_FAX_NUM             AS TollFreeFaxNum,
  pl.TOLL_FREE_PHONE_NUM           AS TollFreePhoneNum,
  pl.UPDATE_DATE                   AS UpdateDate,
  pl.UPDATE_NUM                    AS UpdateNum,
  pl.UPDATE_USER                   AS UpdateUser,
  pl.WEB_SITE_ADDRESS              AS WebSiteAddress,
  pl.EMAIL_ADDR                    AS EmailAddress
FROM PROVIDER_LOCATION pl
WHERE PROVIDER_SEQ_NUM = :ProviderID
```

## Operations and processes
- [Retrieve provider locations by provider or type](../operations/retrieve-provider-locations-by-provider-or-type.md)

## Business rules
- [BR-PLOC-004: Ineligibility period exclusion](../business-rules.md#br-ploc-004-ineligible-locations-are-excluded-from-filtered-lists)

## Sources
- SAIF.Business.View.Provider/clsProviderLocationCollection.vb